import numpy as py 
import pymysql
import pandas as pd
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            database='sjwj',
            port=3306,
            charset='utf8'
        )
        
        
    def is_holiday(self,date):
        """判断是否为节假日"""
        if date in ['2024-09-03', '2024-10-01', '2024-10-02', '2024-10-03', '2024-10-04', '2024-10-05', '2024-10-06', '2024-10-07', '2025-01-01', '2024-01-02', '2024-01-03']:
            return 1
        return 0
        
    def get_data(self):
            cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
            sql = """
            SELECT DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count FROM order_user_gate_rel g WHERE HOUR(g.create_time) BETWEEN 6 and 23 GROUP BY date, hour
            """
            cursor.execute(sql)
            ret = cursor.fetchall()
            df = pd.DataFrame(ret)
            # print(df.head)
            #转换格式
            date_range = pd.date_range(start="2024-07-01", end="2025-03-02", freq="D")
            hours = range(6,24)
            full_index = pd.MultiIndex.from_product([date_range, hours], names=['date','hour'])
            df_full = df .set_index(['date','hour']).reindex(full_index, fill_value=0).reset_index()
            #按天组织数据，每行包含18个小时的检票次数
            df_pivot = df_full.pivot(index='date', columns='hour', values='count')
            # print(df_pivot.head)
            df_pivot['dow'] = df_pivot.index.dayofweek
            df_pivot['month'] = df_pivot.index.month
            df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
            
            # 对星期几和月份进行独热编码
            df_pivot = pd.get_dummies(df_pivot, columns=['dow','month'], dtype=int)
            # print(df_pivot.head())
            # 归一化小时列
            hours_columns = list(range(6, 24))
            df_hours = df_pivot[hours_columns].copy()
            feature_columns = [col for col in df_pivot.columns if col not in hours_columns]
            df_feature = df_pivot[feature_columns].copy()
            scaler = MinMaxScaler()
            scaler_hours = scaler.fit_transform(df_hours)
            dump(scaler,"D:/数据挖掘代码/git第一个项目/five/scaler.joblib")
            # 将归一化后的数据转换为DataFrame
            df_hours_scaled = pd.DataFrame(scaler_hours, columns=hours_columns, index=df_hours.index)
            # 合并
            df_pivot_clean = pd.concat([df_hours_scaled, df_feature], axis=1)
            print(df_pivot_clean)
            df_pivot_clean.to_csv(r"D:/数据挖掘代码/git第一个项目/five/scenic_data.csv",index=False)
            
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()